import datetime
import json
import mimetypes
import os
import re
from io import BytesIO
from shlex import quote

import redis
import requests
import xlsxwriter
from flask import Blueprint, make_response, request, jsonify, redirect
import random
from db import db1, DB
from utils.captcha.captcha import captcha
from utils.jwttoken import myjwt
from utils.mailbox import Phone_Code
from utils.rbac import RBAC

user_view = Blueprint('user_view', __name__)


@user_view.route('/getimagecode/', methods=['GET'])
# 获取验证码图片
async def getimagecode():
    uuid = request.args.get('uuid')
    print(uuid)
    name, text, image = captcha.generate_captcha()
    r = redis.Redis()
    print(text)
    r.set('tuxing', text.lower())
    res = make_response(image)
    res.content_type = 'image/png'
    return res


# 确定图形确定码
@user_view.route('/confirm_mailbox/', methods=['GET'])
# 获取验证码图片
async def confirm_mailbox():
    verification_code = request.args.get('verification_code')
    r = redis.Redis(decode_responses=True)
    if verification_code == '':
        return ({'code': '403', 'msg': '验证码不能为空'})
    code = r.get('tuxing')
    if str(code) == str(verification_code):
        return ({'code': '200', 'msg': '图形验证成功'})
    else:
        return ({'code': '403', 'msg': '验证码错误'})


@user_view.route('/z_reg/', methods=['POST'])
async def z_reg():
    data = request.form
    print(data)
    username = data['username']
    password = data['password']
    password1 = data['password1']
    phone = data['phone']
    phone_code = data['phone_code']
    code = data['code']
    email = data['email']
    email_code = data['email_code']
    r = redis.Redis(decode_responses=True)
    if username != '':
        if not re.match('\w{5,20}$', str(username)):
            return ({'code': 10010, 'msg': '请输入4-40位字符'})
        sql = "select * from user where username='%s'" % (data['username'])
        res = db1.find_all(sql)
        if res:
            return ({"code": 10011, 'msg': '用户名已存在'})
    if password != '':
        if not re.match('\w{6,20}$', str(password)):
            return ({"code": 20020, 'msg': '请输入6-30位字符'})
    if password1 != '':
        if password != password1:
            return ({"code": 20022, 'msg': '两次密码不一致'})
    if code != '':
        res = r.get('tuxing')
        if str(code) != str(res):
            return ({"code": 20023, 'msg': '验证码不正确'})
    if email != '':
        if not re.match('^[a-zA-Z0-9_-]+@[a-zA-Z0-9_-]+(\.[a-zA-Z0-9_-]+)+$', str(email)):
            return ({"code": 20026, 'msg': '请输正确邮箱邮箱'})
    couns = r.get(email)
    if email_code != '':
        if str(couns) != str(email_code):
            print('333')
            return ({"code": 20025, 'msg': '请输正确邮箱验证码'})
    if phone != '':
        if not re.match('^(13[0-9]|14[01456879]|15[0-35-9]|16[2567]|17[0-8]|18[0-9]|19[0-35-9])\d{8}$', str(phone)):
            return ({"code": 20024, 'msg': '请输正确手机号码'})
    num = r.get(phone)
    if phone_code != '':
        if str(num) != str(phone_code):
            return ({"code": 20025, 'msg': '请输正确手机验证码'})

    return ({'code': '200'})


# 发送手机验证码
@user_view.route('/mobile_phone', methods=['GET'])
async def mobile_phone():
    phone = request.args.get('phone')
    if phone == '':
        return ({'code': '403', 'msg': '手机号不能为空'})
    num = random.randint(100000, 999999)
    phones = Phone_Code()
    r = redis.Redis(decode_responses=True)
    codes = phones.send_message(phone, num, 1)
    r.setex(phone, 60, num)
    if codes == 1:
        return ({'code': '200', 'msg': '短信发送成功'})
    else:
        return ({'code': '403', 'msg': '短信发送失败'})


# 账号密码注册
@user_view.route('/reg', methods=['POST'])
async def reg():
    data = request.form
    if data['username'] == '' or data['password'] == '' or data['email'] == '':
        return ({'code': '200', 'msg': '用户名或密码不能为空'})
    sql = "insert into user (username,password,email,roleid) values ('%s','%s','%s',%s)" % (
        data['username'], data['password'], data['email'], 1)
    res1 = db1.update(sql)
    print(res1)
    return ({"code": '200', 'msg': '注册成功'})


# 手机号注册
@user_view.route('/z_mobile', methods=['POST'])
async def z_mobile():
    phone = request.form.get('phone')
    if phone != '':
        print(phone)
        db = DB()
        db.insert("insert into user (mobile,roleid) values ('%s',%s)" % (phone, 1))
        return ({'code': '200', 'msg': '注册成功'})
    else:
        return ({'code': '403', 'msg': '手机号不能为空'})


# 邮箱验证码
@user_view.route('/post_right', methods=['POST'])
async def post_right():
    email = request.form.get('email')
    if email == '':
        return ({'code': '403', 'msg': '邮箱不能为空'})
    posc_rice = Phone_Code()
    num = random.randint(100000, 999999)
    title = '邮箱验证注册'
    centend = '您好,您的验证码为%s,请在1分钟内验证' % num
    r = redis.Redis(decode_responses=True)
    r.setex(email, 60, num)
    codes = posc_rice.send_mail(email, title, centend)
    if codes == 2:
        return ({'code': '200', 'msg': '邮箱发送成功'})
    else:
        return ({'code': '403', 'msg': '邮箱发送失败'})


# git第三方登录
@user_view.route('/git_ee')
async def git_ee():
    return ({
        'url': "https://gitee.com/oauth/authorize?client_id=689562c96a054a2fc3b18de1c2485c78037d551a4f8434005ae984c91349d7ce&redirect_uri=http://localhost:5000/gitee_back/&response_type=code"})


@user_view.route('/gitee_back/')
async def gitee_back():
    code = request.args.get('code')
    print(code)
    # 发情请求 获取token
    res = requests.post(
        "https://gitee.com/oauth/token?grant_type=authorization_code&code=%s&client_id=689562c96a054a2fc3b18de1c2485c78037d551a4f8434005ae984c91349d7ce&redirect_uri=http://localhost:5000/gitee_back/&client_secret=4ea25d8263584b1009b0df92faead2daa56e829df0ccbab61e412050ba39759b" % code)
    print(res.text)
    # 获取token    # 转为json格式   然后根据键区出来token
    token = res.json()['access_token']
    # 获取到token之后交换用户的信息
    res = requests.get("https://gitee.com/api/v5/user?access_token=%s" % token)
    print(res.text)
    # 获取名称
    username = res.json()['name']
    print(username)
    avatar_url = res.json()['avatar_url']
    password = res.json()['login']
    print(avatar_url)
    # 入库操作
    db = DB()
    res = db.get_one("select * from user where username='%s'" % username)

    if res:
        return (
            {'code': 200, 'msg': '登录成功', 'id': res['id'], 'username': res['username'],
             'login_hours': res['login_hours'],
             'account_one': res['account_one']})
    else:
        db.insert("insert into user (username,password) values ('%s','%s')" % (username, password))
        res = db.get_one("select * from user where username='%s'" % username)
    return (
        {'code': '200', 'msg': '登录成功', 'id': res['id'], 'username': res['username'], 'login_hours': res['login_hours'],
         'account_one': res['account_one']})


# 账号密码登录

@user_view.route('/account_password_login', methods=['POST'])
async def account_password_login():
    username = request.form.get('username')
    password = request.form.get('password')
    verification_code = request.form.get('verification_code')
    print(verification_code)
    if username == '':
        return ({'code': '403', 'msg': '用户名不能为空'})
    if password == '':
        return ({'code': '403', 'msg': '密码不能为空'})
    if verification_code == '':
        return ({'code': '403', 'msg': '验证码不能为空'})
    r = redis.Redis(decode_responses=True)
    num = r.get('tuxing')
    print(num)
    print(num)
    print(num)
    if str(num) != str(verification_code):
        return ({'code': '403', 'msg': '验证码不正确'})
    db = DB()
    res = db.get_one("select * from user where username='%s'" % username)
    print(res)
    if res:
        if str(username) == str(res['username']) and str(password) == str(res['password']) and int(res['roleid']) == 1:
            user = {'id': res['id'], 'username': username}
            token = myjwt.jwt_encode(user)
            print({'code': 200, 'msg': '登陆成功', 'username': res['username'], 'id': res['id'], 'token': token,
                   'login_hours': res['login_hours'], 'account_one': res['account_one']})
            return jsonify({'code': 200, 'msg': '登陆成功', 'username': res['username'], 'id': res['id'], 'token': token,
                            'login_hours': res['login_hours'], 'account_one': res['account_one']})
        else:
            return jsonify({'code': 403, 'msg': '用户名或密码不正确'})
    else:
        return jsonify({'code': 403, 'msg': '用户不存在'})


@user_view.route('/account_code', methods=['POST'])
async def account_code():
    verification_code = request.form.get('verification_code')
    if verification_code == '':
        return ({'code': '403', 'msg': '验证码不能为空'})
    r = redis.Redis(decode_responses=True)
    num = r.get('tuxing')
    if str(num) != str(verification_code):
        return ({'code': '403', 'msg': '验证码不正确'})
    return ({'code': '200'})


# 获取用户权限
@user_view.route('/user_permissions', methods=['GET'])
async def user_permissions():
    id = request.args.get('id')
    res = RBAC()
    data = res.get_pagedef_reslist(id)
    return ({'code': '200', 'res': data})


from flask import Blueprint
from utils.comm import get_data
from db import db1

dp_account = Blueprint('account', __name__)


# 开户
@user_view.route('/adduser', methods=['POST'])
def adduser():
    data = request.form
    sql = "insert into account(user_id,mobile,card_id,name,age,gender,id_number,id_front,id_reverse) values(%d,'%s',%d,'%s',%d,'%s','%s','%s','%s')" % (
        data['user_id'], data['mobile'], data['card_id'], data['name'], data['age'], data['gender'], data['id_number'],
        data['id_front'], data['reverse'])
    db1.insert(sql)
    return ({"code": 200, "msg": "添加成功"})


import requests


# p2p开户
@user_view.route('/openbank', methods=['POST'])
def openbank():
    data = request.json
    # 调用银行存管系统
    id_front = data['imgs'][0]
    id_reverse = data['imgs'][1]
    res = requests.post('http://localhost:8000/openb/', data)
    code = eval(res.text)
    if str(code['code']) == '200':
        # 生成开户改成审核中
        sql = "insert into account (user_id,mobile,name,age,gender,id_number,id_front," \
              "id_reverse,stats) values (%d,'%s','%s',%d,'%s','%s','%s','%s',%d)" % (
                  int(data['user_id']), data['mobile'], data['name'], int(data['age']), data['gender'],
                  data['id_number']
                  , id_front, id_reverse, 1
              )
        db1.update(sql)
        return jsonify({'code': 200, 'msg': "审核中"})
    else:
        return jsonify({'code': 10010, 'msg': "银行业务繁忙"})


@user_view.route('/updateopen')
async def updateopen():
    state = request.args.get('state')
    name = request.args.get('name')
    print(name)
    db = DB()
    res = db.get_one("select * from account where  name='%s'" % str(name))
    if res:
        sql = "update account set stats=%d where name='%s'" % (int(state), str(name))
        db1.update(sql)
        return ({'code': '200'})
    else:
        return ({'code': '400'})


# 上传图片
@user_view.route('/upload', methods=['POST'])
def uploads():
    print(request.files)
    fileilename = request.files['file']
    print(fileilename)
    # print(request.files['file'])/
    fileilename.save(os.path.join('./static/' + fileilename.filename))
    return {'code': '200', 'filename': fileilename.filename}


# 评测获取
@user_view.route('/reciews', methods=['GET'])
def reciews():
    db = DB()
    data = db.get_all("SELECT * FROM `assess_topic` ORDER BY RAND() LIMIT 10")
    for i in data:
        i['answer'] = eval(i['answer'])
    return ({'code': '200', 'data': data})


# 评估分数比对结果
@user_view.route('/assess_grade', methods=['POST'])
def assess_grade():
    sum1 = 0
    data = request.json
    print(data)
    if len(data['data']) < 10:
        return jsonify({'code': 10010, 'msg': '题目填写不完整'})
    else:
        for i, j in data['data'].items():
            sum1 += int(j)
            print(sum1)
        try:
            sql = "select type from assess_grade where %d > grade_rise and %d <= grade_end" % (sum1, sum1)
            res = db1.find(sql)
            print('5959')
            sql1 = "update user set assess_type='%s' where id=%d" % (res['type'], int(data['uid']))
            db = DB()
            db.update("update user set login_hours=login_hours+1 where  id=%d" % int(data['uid']))
            db1.update(sql1)
            return jsonify({'code': 200, "msg": '您是' + res['type']})
        except Exception as e:
            print(e)
            return jsonify({'code': 400, 'msg': '数据有误'})


# 个人中心
# 获取用户个人信息
@user_view.route('/personal_information/', methods=['GET'])
def personal_information():
    uid = request.args.get('uid')
    print(uid)
    print(uid)
    print(uid)
    db = DB()
    data = db.get_one("select * from user where id=%d" % int(uid))
    print(data)
    return ({'code': 200, 'list': data})


# 添加头像
@user_view.route('/upload_avatar', methods=['get'])
def upload_avatar():
    img = request.args.get('img')
    uid = request.args.get('uid')
    nicheng = request.args.get('nicheng')
    db = DB()
    if nicheng:
        db.update("update user set nickname='%s' where id=%d" % (nicheng, int(uid)))
    if img:
        db.update("update user set pic='%s' where id=%d" % (img, int(uid)))
    return ({'code': 200, 'msg': '添加成功'})


# ...................充值.......................
# 获取所有个人银行卡
@user_view.route('/get_bank_card', methods=['get'])
def get_bank_card():
    uid = request.args.get('uid')
    db = DB()
    data = db.get_all(
        "select * from addbankcard INNER JOIN  bank_card on addbankcard.bankname_id=bank_card.id where addbankcard.userid=%s" % int(
            uid))
    for i in data:
        i['card_name'] = str(i['name']) + '(' + str(i['card_number'][-4:]) + ')'
    return ({"code": '200', 'data': data})


@user_view.route('/get_user', methods=['get'])
def get_user():
    uid = request.args.get('uid')
    code = request.args.get('code')
    db = DB()
    data = db.get_one("select * from user where id=%s" % uid)
    if str(code) == str(data['pay_password']):
        return ({"code": '200', 'msg': '支付密码正确'})
    else:
        return ({"code": '403', 'msg': '支付密码错误'})


# 充值金额及选择
@user_view.route('/recharge_mode', methods=['post'])
def recharge_mode():
    mode = request.form.get('mode')
    uid = request.form.get('uid')
    price = request.form.get('price')
    node = mode[:6]
    db = DB()
    nums_data = db.get_one("select * from user where id='%s' and account_one=1" % uid)
    if nums_data:
        if str(node) == '621098':
            db = DB()
            data = db.get_one("select * from china_bank where card_number='%s' and money>%s" % (mode, price))
            if data:
                order_number = 'key' + datetime.datetime.strftime(datetime.datetime.now(), '%Y%m%d%H%M')
                print(order_number)
                num_data = {'order_number': order_number, 'ban_number': str(mode), 'money': float(price), 'state': 1,
                            'balance': '', 'userid': int(uid)}
                res = requests.post('http://localhost:8000/openb_bank/', num_data)
                print(res.text)
                return ({'code': '200', 'msg': '充值成功'})
            else:
                return ({'code': '403', 'msg': '余额不足'})
        return ({'code': '403', 'msg': '此银行没合作,请您换想对应银行'})
    else:
        return ({'code': '404', 'msg': '没开户'})


# 更改审核充值状态
@user_view.route('/bankopens')
async def bankopens():
    state = request.args.get('state')
    id = request.args.get('id')
    print(id)
    db = DB()
    res = db.get_one("select * from order_details where  id='%s'" % int(id))
    if res:
        sql = "update account set stats=%d where id=%s" % (int(state), int(id))
        db1.update(sql)
        return ({'code': '200'})
    else:
        return ({'code': '400'})


# .........银行卡............

# 获取银行名称接口
@user_view.route('/bankname_list')
def bankname_list():
    sql = "select * from bank_card"
    res = db1.find_all(sql)
    return jsonify({'code': 200, 'list': res})


import requests
import re


# 添加银行卡
@user_view.route('/addbankcard', methods=['POST'])
def addbankcard():
    data = request.json
    if not re.match('\d{18}$', str(data['card_number'])):
        return ({'code': 10010, 'msg': '请输入18位数字'})
    if not re.match('\d{6}$', str(data['pay_password'])):
        return ({'code': 10010, 'msg': '请输入6位数字'})
    if not re.match('1[3-9]\d{9}$', str(data['mobile'])):
        return ({'code': 10010, 'msg': '请输入正确手机号'})

    sql1 = "select name from bank_card where id=%d" % int(data['bankname_id'])

    res1 = db1.find(sql1)
    print(res1)
    data['card_number'] = data['card_number']
    # 调用银行存管系统
    res = requests.post('http://localhost:8000/addcard/', data)
    code = eval(res.text)
    if str(code['code']) == '200':
        # 生成添加银行卡
        sql = "insert into addbankcard (card_number,mobile,bankname_id,userid) values ('%s','%s',%d,%d)" % (
            data['card_number'], data['mobile'], int(data['bankname_id']), int(data['userid'])
        )
        db1.insert(sql)
        sql2 = "update user set pay_password=%d where id=%d" % (int(data['pay_password']), int(data['userid']))
        db1.update(sql2)
        return jsonify({'code': 200, 'msg': "审核中"})
    else:
        return jsonify({'code': 10010, 'msg': "银行业务繁忙"})


# 修改银行卡状态
@user_view.route('/updatcard')
def updatcard():
    stats = request.args.get('stats')
    card_number = request.args.get('card_number')
    sql = "update addbankcard set stats=%d where card_number='%s'" % (int(stats), card_number)
    db1.update(sql)
    return jsonify({'code': 200, 'msg': "添加银行卡成功"})


@user_view.route('/delmybank')
def delmybank():
    pay_password = request.args.get('pay_password')
    id = request.args.get('id')
    sql = "select id from user where pay_password='%s'" % pay_password
    res = db1.find(sql)
    if res:
        sql1 = "delete from addbankcard where id=%d" % int(id)
        db1.update(sql1)
        return jsonify({'code': 200, 'msg': '解绑成功'})
    else:
        return jsonify({'code': 4001, 'msg': '密码不正确'})


@user_view.route('/withdrawal', methods=['POST'])
def withdrawal():
    data = get_data()
    user_id = data['user_id']
    mode = data['mode']  # 银行卡号
    price = data['price']
    print(price)
    password = data['password']
    sql = "select * from user where id={}".format(
        int(user_id))
    db = DB()
    res = db.get_one(sql)
    if str(res['pay_password']) == str(password):
        # 判断是否开户
        if int(res['account_one']) == 1:
            # 生成订单详情
            print(int(res['money']))
            if int(res['money']) > float(price):
                order_number = 'key' + datetime.datetime.strftime(datetime.datetime.now(), '%Y%m%d%H%M')
                print(order_number)
                num_data = {'order_number': order_number, 'ban_number': str(mode), 'money': float(price), 'state': 2,
                            'balance': '', 'userid': int(user_id)}
                res = requests.post('http://localhost:8000/Withdrawal_details/', num_data)
                return ({'code': '200', 'msg': '提现成功'})
            else:
                return ({'code': '403', 'msg': '余额不足'})
        return ({'code': '403', 'msg': '请先去开户'})
    return ({'code': '403', 'msg': '支付密码错误'})


# 发布标的接口
@user_view.route('/addmark', methods=['POST', 'GET'])
def addmark():
    data = request.json
    print(data)
    sql = "insert into markid values (0)"
    id = int(db1.update(sql))
    number = id % 3
    # interest_rate=float(data['interest_rate'])
    print(float(data['interest_rate']))
    print('1111')
    print("insert into mark%d (id,userid,name,total_money,status,repay_time,use1,balance_money,interest_rate) " \
          "values(%d,%d,'%s',%d,%d,%d,'%s',%s,%d)" \
          % (number, id, int(data['userid']), data['name'], int(data['money']), 1, int(data['time']), data['use1'],
             int(data['money']), float(data['interest_rate'])))
    sql1 = "insert into mark%d (id,userid,name,total_money,status,repay_time,use1,balance_money,interest_rate) " \
           "values(%d,%d,'%s',%d,%d,%d,'%s',%s,%s)" \
           % (number, id, int(data['userid']), data['name'], int(data['money']), 1, int(data['time']), data['use1'],
              int(data['money']), float(data['interest_rate']))
    db1.update(sql1)
    return jsonify({'code': 200, 'msg': '发起借款成功'})


# 投标
@user_view.route('/bidmark', methods=['POST'])
def bidmark():
    data = get_data()
    print(data)
    time = datetime.datetime.strftime(datetime.datetime.now(), '%Y-%m-%d-%H:%M:%S')
    num = random.randint(1000, 9999)
    order_number = str(datetime.datetime.strftime(datetime.datetime.now(), '%Y%m%d%H%M%S')) + str(num)
    money = int(data['money'])
    id = data['userid']
    sql = 'select * from user where id=%d' % (int(id))
    res = db1.find(sql)
    bd = DB()
    print(res)
    money2 = res['money']
    print(money2)
    nums = int(data['num']) % 3
    # 获取这个订单详情
    # 判断投资金额是否大于集齐满标金额
    one = db1.get_one("select * from mark%d where id=%s" % (nums, int(data['num'])))
    if money2 >= money:
        if one['balance_money'] >= int(data['money']):
            # 添加投标订单详情
            sql2 = "insert into recordbid (userid,money,datetime,state,Investment_id,order_number) values(%d,%f,'%s',1,%s,%s)" % (
                int(id), money, time, int(data['num']), order_number)  # 1冻结资金
            db1.insert(sql2)
            # 查到相对应的表 修改已筹集资金
            bd.update("update mark%d set need_money=need_money+%s,balance_money=balance_money-%s where id=%s" % (
                nums, int(data['money']), int(data['money']), int(data['num'])))
            money1 = money2 - money
            sql1 = 'update user set money=%d where id=%d' % (money1, int(id))
            db1.update(sql1)
            # 投资之后生成账单详情
            db1.insert(
                "insert into order_details (order_number,money,state,balance,userid,stats) values ('%s',%s,3,%s,%s,1)" % (
                    order_number, float(data['money']), money, int(data['userid'])))
            # 查询是否满标
            data_one = db1.get_one("select * from mark%d where id=%s and balance_money=0" % (nums, int(data['num'])))
            if data_one:
                # 如果满标时更改状态为3
                # 状态1没审核2通过开始3满标4审核通过5没通过6待还款7已经完成8逾期
                bd.update("update mark%d set status=3,full_time='%s' where id=%s" % (nums, time, int(data['num'])))
            return jsonify({'code': 200, 'msg': '投资成功'})
        else:
            return jsonify({'code': '403', 'msg': '投资金额较大,剩余金额为%s' % one['balance_money']})
    else:
        return jsonify({'msg': '资金不足'})


@user_view.route('/get_marklist')
def marklist():
    nuid = request.args.get('nuid')
    # 获取未满表的时候
    sql = "select * from mark0 where status=%d union all " \
          "select * from mark1 where status=%d union all " \
          "select * from mark2 where status=%d" % (int(nuid), int(nuid), int(nuid))
    db=DB()
    res = db.get_all(sql)
    return jsonify({'code': 200, 'list': res})


# 定时任务导包
from apscheduler.schedulers.background import BackgroundScheduler


# 还款提醒
# 自动还款
def warn(userid):
    # 获取用户需还款订单
    sql = "select * from mark0 where userid=%d and status=4 union all " \
          "select * from mark1 where userid=%d and status=4 union all " \
          "select * from mark2 where userid=%d and status=4" % (int(userid), int(userid), int(userid))
    res = db1.find_all(sql)
    # 循环获取用户详情
    for i in res:
        sql1 = "select * from user where id=%d" % int(i['userid'])
        res1 = db1.find(sql1)
        time = i['full_time'].split('-')
        if int(time[1]) + int(i['repay_time']) > 12:
            time[0] = str(int(time[0]) + 1)
            print(time[2])
        else:
            time[1] = '0' + str(int(time[1]) + int(i['repay_time']))
        print("-".join(time))
        # 现在一周前的时间
        # dt6 = datetime.datetime.now() - datetime.timedelta(weeks=1)
        time2 = datetime.datetime.strftime(datetime.datetime.now(), '%Y-%m-%d %H:%M:%S')
        num = random.randint(1000, 9999)
        order_number = str(datetime.datetime.strftime(datetime.datetime.now(), '%Y%m%d%H%M%S')) + str(num)
        print(time2)
        if str("-".join(time)) == str(time2):
            # 到期自动还款及发送邮件
            num = int(i['id']) % 3
            # 更改还款状态
            sql2 = "update mark%d set status=6 where id = %d" % (int(num), int(i['id']))
            db1.update(sql2)
            # 获取用户还款金额扣除从账户扣除还款金额
            data = db1.get_one("select * from mark%d where id=%s" % (int(num), int(i['id'])))
            num_money = ((data['total_money'] * data['interest_rate']) / 12 * int(data['repay_time'])) + int(
                data['total_money'])
            print(num_money)
            db1.update("update user set money=money-%s where id=%s" % (num_money, int(userid)))
            # 还款用户钱
            sql1 = "select * from user where id=%d" % int(i['userid'])
            res1 = db1.find(sql1)
            db1.insert(
                "insert into order_details (order_number,money,state,balance,userid,stats) values ('%s',%s,6,%s,%s,1)" % (
                    order_number, num_money, res1['money'], int(i['userid'])))
            # 还款投资用户的钱
            res2 = db1.find_all("select * from recordbid where Investment_id=%d" % int(i['id']))
            for j in res2:
                j['money'] += float(j['money']) * float(i['interest_rate'])
                print(i['money'])
                sql3 = "update user set money=money+%s where id=%d" % (float(j['money']), int(j['userid']))
                db1.update(sql3)
                # 增加各个用户的钱及利息
                # 查询用户的余额
                res_money = db1.find("select * from user where id=%d" % int(j['userid']))
                db1.insert(
                    "insert into order_details (order_number,money,state,balance,userid,stats) values ('%s',%s,7,%s,%s,1)" % (
                        order_number, j['money'], res_money['money'], int(j['userid'])))

            # 发送邮件认证
            p = Phone_Code()
            email = res1['email']
            # email = "2409248564@qq.com"
            title = "到期还款提醒"
            count = "欢迎使用，点击以下< a href=' '>链接</ a>进行充值"
            p.send_mail(email, title, count)


# 向调度器中添加定时任务
# timelist = datetime.datetime.strftime(datetime.datetime.now(),'%Y-%m-%d').split('-')
# scheduler.add_job(warn, 'date', run_date=datetime.date(int(timelist[0]), int(timelist[1]), int(timelist[2])))
@user_view.route('/warn_time')
def warn_time():
    userid = request.args.get('userid')
    scheduler = BackgroundScheduler(timezone="Asia/Shanghai")
    scheduler.add_job(warn, 'interval', args=(userid), seconds=1)
    # 启动定时任务调度器工作
    scheduler.start()
    return ({'code': '200'})


# 满标还款
@user_view.route('/full_mark', methods=['POST'])
def full_mark():
    data = get_data()
    # 获取投资订单
    sql = "select * from recordbid where Investment_id=%d" % int(data['markid'])
    res = db1.find_all(sql)
    num_code = int(data['markid']) % 3
    # 获取总共借款订单
    sql1 = "select * from mark%d where id=%d" % (int(num_code), int(data['markid']))
    res1 = db1.find(sql1)
    # 获取用户
    sql2 = "select * from user where id=%d" % int(data['userid'])
    res2 = db1.find(sql2)
    print(data)
    if str(data['pay_password']) != str(res2['pay_password']):
        return jsonify({'code': 4001, "msg": "支付密码错误"})
    num = float(res1['total_money']) + (float(res1['total_money']) * float(res1['interest_rate']))
    if float(data['money']) != num:
        return jsonify({'code': 4002, "msg": '请输入正确金额'})
    if float(data['money']) > float(res2['money']):
        return jsonify({'code': 4003, "msg": '账户余额不足请去充值'})
    num = random.randint(1000, 9999)
    # 生成订单详情
    order_number = str(datetime.datetime.strftime(datetime.datetime.now(), '%Y%m%d%H%M%S')) + str(num)
    for i in res:
        i['money'] = i['money'] + (float(i['money']) * float(res1['interest_rate']))
        print(i['money'])
        # 还款各个用户的钱及利息
        sql3 = "update user set money=money+%s where id=%d" % (float(i['money']), int(i['userid']))
        db1.update(sql3)
        # 增加各个用户的钱及利息
        # 查询用户的余额
        res_money = db1.find("select * from user where id=%d" % int(i['userid']))
        db1.insert(
            "insert into order_details (order_number,money,state,balance,userid,stats) values ('%s',%s,7,%s,%s,1)" % (
                order_number, i['money'], res_money['money'], int(i['userid'])))
        # 扣除用户中的余额
        sql4 = "update user set money=money-%s where id=%d" % (
            float(data['money']), int(data['userid']))
        db1.update(sql4)
        res_money = db1.find("select * from user where id=%d" % int(i['userid']))
        db1.insert(
            "insert into order_details (order_number,money,state,balance,userid,stats) values ('%s',%s,6,%s,%s,1)" % (
                order_number, data['money'], res_money['money'], int(data['userid'])))
        # 更改用户状态为还款完成
        sql5 = "update mark%d set status=7 where id=%d" % (int(num_code), int(data['markid']))
        db1.update(sql5)
        # 增加各个用户的订单详情
    return jsonify({'code': 200, 'msg': '还款成功'})


# 待还款
@user_view.route('/repaylist')
def repaylist():
    userid = int(request.args.get('userid'))
    sql = "select * from mark0 where userid=%d and status=4 union all " \
          "select * from mark1 where userid=%d and status=4 union all " \
          "select * from mark2 where userid=%d and status=4" % (userid, userid, userid)
    res = db1.find_all(sql)
    for i in res:
        print(i['total_money'])
        print(i['interest_rate'])
        num = (float(i['total_money'])) * float(i['interest_rate'])
        print(num)
        i['repay'] = float(i['total_money']) + num
    print(res)
    return jsonify({'code': 200, 'list': res})


# 购买基金
@user_view.route('/add_fund')
def add_fund():
    userid = int(request.args.get('userid'))
    day = int(request.args.get('day'))
    password = request.args.get('password')
    price = request.args.get('price')
    num_id = int(request.args.get('num_id'))
    status = int(request.args.get('status'))
    data = db1.get_one("select * from user where id=%s and pay_password=%s" % (userid, password))
    if data:
        num = random.randint(1000, 9999)
        order_number = str(datetime.datetime.strftime(datetime.datetime.now(), '%Y%m%d%H%M%S')) + str(num)
        add_time = datetime.datetime.strftime(datetime.datetime.now(), '%Y-%m-%d 00:00:00')
        end_time = (datetime.datetime.now() - datetime.timedelta(days=-day)).strftime("%Y-%m-%d 00:00:00")
        num_or = db1.get_one("select * from china_fund where id=%s" % num_id)
        print(num_or)
        print("insert into my_fund (order_number,bank_id,china_funt,status,add_time,end_time,input_time,money)"
              " values ('%s',%s,'%s',%s,'%s','%s',%s,%s)" % (
                  order_number, num_id, num_or['model'], status, add_time, end_time, day, float(price)))
        db1.insert(
            "insert into my_fund (order_number,bank_id,china_funt,status,add_time,end_time,input_time,money,userid)"
            " values ('%s',%s,'%s',%s,'%s','%s',%s,%s,%s)" % (
                order_number, num_id, num_or['name'], status, add_time, end_time, day, float(price), userid))
        sql4 = "update user set money=money-%s where id=%d" % (
            float(price), int(userid))
        db1.update(sql4)
        res_money = db1.find("select * from user where id=%d" % int(userid))
        db1.insert(
            "insert into order_details (order_number,money,state,balance,userid,stats) values ('%s',%s,8,%s,%s,1)" % (
                order_number, price, res_money['money'], int(userid)))
        return jsonify({'code': 200, 'msg': '购买成功'})
    else:
        return jsonify({'code': 403, 'msg': '支付密码错误'})


from dateutil.relativedelta import relativedelta

@user_view.route('/my_collector')
def my_collector():
    sql = "select * from mark0  where status=6 union all " \
          "select * from mark1  where status=6 union all " \
          "select * from mark2  where status=6"
    res = db1.find_all(sql)

    dt = datetime.datetime.strftime(datetime.datetime.now(), '%Y-%m-%d 00:00:00')
    dt = datetime.datetime.strptime(dt, '%Y-%m-%d %H:%M:%S')
    print(res)
    for  i  in  res:
        print(i['full_time'])
        i['full_time'] = datetime.datetime.strptime(i['full_time'], '%Y-%m-%d %H:%M:%S')
        now_time = (i['full_time'] + relativedelta(months=+int(i['repay_time'])))
        print(now_time)
        if now_time==dt:
            one_date=db1.get_one("select * from user where id=%s" % i['userid'])
            db1.insert(
                "insert into collection_form (expiration_time,name,userid,money,interest_rate,statur) values ('%s','%s',%s,%s,%s,1)" % (
                    i['full_time'], one_date['username'], i['userid'],float(i['total_money']),float(i['interest_rate']) ))
    return ({'code':'200'})



@user_view.route('/get_collector')
def get_collector():
    data=db1.get_all("select * from collection_form")
    for i in data:
        i['expiration_time'] = str(i['expiration_time'])
    print(data)
    return ({'code': '200','data':data})
@user_view.route('/get_User_details')
def get_User_details():
    userid=request.args.get('userid')
    data=db1.get_one("select * from user where id=%s" % userid)
    return ({'code': '200','data':data})

@user_view.route('/update_User_details')
def update_User_details():
    nuid=request.args.get('nuid')
    db1.update("update collection_form set statur=2 where id=%s" % nuid)
    return ({'code': '200'})


from io import BytesIO
from xlrd import open_workbook
from urllib.parse import quote
import xlsxwriter
import datetime
import mimetypes
@user_view.route('/download', methods=["GET", "POST"])

def download_pass():
    # 实例化二进制数据
    out = BytesIO()
    # 创建一个Excel实例
    workbook = xlsxwriter.Workbook(out)
    # 添加一个sheet，add_worksheet()，默认"Sheet1"，可自定义命名，add_worksheet("资产列表")
    table = workbook.add_worksheet()
    # table.write(行, 列, 字段名)
    # 行、列从 0 开始计数
    # 字段命名根据每个人使用情况而定，插入数据部分也是
    sql = "select * from collection_form"
    db=DB()
    res = db.get_all(sql)
    table.write(0, 0, "还款时间")
    table.write(0, 1, "用户名")
    table.write(0, 2, "还款金额")
    table.write(0, 3, "利率")
    table.write(0, 4, "催促")
    num = 1
    for i in res:
        table.write(num, 0, i['expiration_time'])
        table.write(num, 1, i['name'])
        table.write(num, 2, i['userid'])
        table.write(num, 3, i['money'])
        table.write(num, 4, i['interest_rate'])
        print(i['expiration_time'])

        num += 1

    workbook.close()
    filename = quote("催收记录_{}.xlsx".format(datetime.datetime.now().strftime("%Y%m%d%H%M%S")))

    file = make_response(out.getvalue())
    out.close()
    mime_type = mimetypes.guess_type('%s.xlsx' % filename)[0]
    print(mime_type)
    file.headers['Content-Type'] = mime_type
    file.headers["Cache-Control"] = "no-cache"
    print('8888')
    file.headers['Content-Disposition'] = 'attachment;filename={0};filename*={0}'.format(quote(filename))

    print('8888')
    return file

#
@user_view.route('/downloads', methods=["GET", "POST"])
def downloads():

    return ({'url':'http://localhost:5000/download'})